const express = require('express');
const router = express.Router();
const fs = require('fs');
const path = require('path');
const { v4: uuidv4 } = require('uuid');
const ExcelJS = require('exceljs');
const db = require('../db');
const FreightCalculator = require('../utils/freightCalculator');

// 确保临时目录和结果目录存在
const TEMP_DIR = process.env.TEMP_DIR || './temp';
const RESULTS_DIR = process.env.RESULTS_DIR || './results';
const BATCH_SIZE = parseInt(process.env.BATCH_SIZE) || 500;

/**
 * 上传Excel文件并开始批量测算
 */
router.post('/calculate/batch', async (req, res) => {
  try {
    // 检查是否有文件上传
    if (!req.files || !req.files.excelFile) {
      return res.status(400).json({
        success: false,
        error: '请上传Excel或CSV文件'
      });
    }
    
    const excelFile = req.files.excelFile;
    const fileExt = path.extname(excelFile.name).toLowerCase();
    
    // 验证文件类型
    if (!['.xlsx', '.xls', '.csv'].includes(fileExt)) {
      return res.status(400).json({
        success: false,
        error: '仅支持.xlsx、.xls和.csv格式的文件'
      });
    }
    
    // 生成唯一任务ID
    const taskId = uuidv4();
    const tempFilePath = path.join(TEMP_DIR, `${taskId}${fileExt}`);
    const resultFilePath = path.join(RESULTS_DIR, `${taskId}_result.xlsx`);
    
    // 保存上传的文件
    await excelFile.mv(tempFilePath);
    
    // 异步处理文件，不阻塞响应
    processExcelFile(tempFilePath, resultFilePath, taskId)
      .then(() => {
        console.log(`✅ 任务${taskId}处理完成`);
      })
      .catch(err => {
        console.error(`❌ 任务${taskId}处理失败:`, err.message);
      });
    
    // 立即返回任务ID，前端可通过此ID查询结果
    res.json({
      success: true,
      data: {
        taskId: taskId,
        message: '文件已接收，正在处理中，请稍后查询结果',
        estimatedTime: `约${Math.ceil(excelFile.size / (1024 * 1024 * 5))}分钟（视文件大小而定）`
      }
    });
  } catch (err) {
    res.status(500).json({
      success: false,
      error: err.message
    });
  }
});

/**
 * 查询任务处理状态
 */
router.get('/result/status/:taskId', (req, res) => {
  try {
    const taskId = req.params.taskId;
    const resultFilePath = path.join(RESULTS_DIR, `${taskId}_result.xlsx`);
    
    // 检查结果文件是否存在
    if (fs.existsSync(resultFilePath)) {
      return res.json({
        success: true,
        status: 'completed',
        message: '处理完成，可下载结果',
        hasResult: true
      });
    }
    
    // 检查临时文件是否存在（判断任务是否正在处理）
    const tempFiles = fs.readdirSync(TEMP_DIR).filter(file => file.startsWith(taskId));
    if (tempFiles.length > 0) {
      return res.json({
        success: true,
        status: 'processing',
        message: '正在处理中，请稍后再试'
      });
    }
    
    // 任务不存在或已过期
    res.json({
      success: true,
      status: 'not_found',
      message: '任务不存在或已过期',
      hasResult: false
    });
  } catch (err) {
    res.status(500).json({
      success: false,
      error: err.message
    });
  }
});

/**
 * 下载测算结果
 */
router.get('/result/download/:taskId', (req, res) => {
  try {
    const taskId = req.params.taskId;
    const resultFilePath = path.join(RESULTS_DIR, `${taskId}_result.xlsx`);
    
    if (!fs.existsSync(resultFilePath)) {
      return res.status(404).json({
        success: false,
        error: '结果文件不存在或已过期'
      });
    }
    
    // 设置下载响应头
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename="freight_calculation_result_${taskId}.xlsx"`);
    
    // 流式传输文件
    const fileStream = fs.createReadStream(resultFilePath);
    fileStream.pipe(res);
  } catch (err) {
    res.status(500).json({
      success: false,
      error: err.message
    });
  }
});

/**
 * 处理Excel文件的核心函数
 * @param {string} inputPath - 输入文件路径
 * @param {string} outputPath - 输出文件路径
 * @param {string} taskId - 任务ID
 */
async function processExcelFile(inputPath, outputPath, taskId) {
  try {
    // 加载所有省份和费率段信息（一次性加载到内存，提高处理速度）
    const provinces = db.all(`SELECT id, name, calc_type FROM provinces`);
    if (provinces.length === 0) {
      throw new Error('没有配置任何省份信息，无法进行测算');
    }
    
    // 为每个省份创建计算器并缓存
    const provinceCalculators = new Map();
    for (const province of provinces) {
      const rates = db.all(`
        SELECT weight_min, weight_max, rate, base_fee, sort_num 
        FROM freight_rates 
        WHERE province_id = ?
      `, [province.id]);
      
      if (rates.length > 0) {
        try {
          const calculator = new FreightCalculator(province, rates);
          provinceCalculators.set(province.name, calculator);
          provinceCalculators.set(province.id.toString(), calculator); // 同时支持ID查询
        } catch (err) {
          console.warn(`⚠️ 省份"${province.name}"的费率配置无效，将跳过该省份的数据:`, err.message);
        }
      }
    }
    
    if (provinceCalculators.size === 0) {
      throw new Error('没有有效的费率配置，无法进行测算');
    }
    
    // 创建工作簿和工作表
    const workbook = new ExcelJS.Workbook();
    let worksheet;
    
    // 根据文件类型读取
    const fileExt = path.extname(inputPath).toLowerCase();
    if (fileExt === '.csv') {
      await workbook.csv.readFile(inputPath, { parserOptions: { header: true } });
      worksheet = workbook.getWorksheet(1);
    } else {
      await workbook.xlsx.readFile(inputPath);
      worksheet = workbook.getWorksheet(1); // 获取第一个工作表
    }
    
    // 获取表头并添加结果列
    const headerRow = worksheet.getRow(1);
    headerRow.getCell(headerRow.cellCount + 1).value = '测算运费(元)';
    headerRow.getCell(headerRow.cellCount + 1).value = '状态';
    headerRow.font = { bold: true };
    
    // 记录列索引（支持"省份名称"或"省份ID"，"重量(kg)"，"件数"）
    let provinceColIndex = -1;
    let weightColIndex = -1;
    let pieceCountColIndex = -1;
    
    // 查找列索引
    headerRow.eachCell((cell, colNumber) => {
      const value = String(cell.value || '').trim().toLowerCase();
      if (provinceColIndex === -1 && 
          (value.includes('省份') || value.includes('地区') || value.includes('province'))) {
        provinceColIndex = colNumber;
      } else if (weightColIndex === -1 && 
                (value.includes('重量') || value.includes('kg') || value.includes('weight'))) {
        weightColIndex = colNumber;
      } else if (pieceCountColIndex === -1 && 
                (value.includes('件数') || value.includes('数量') || value.includes('piece'))) {
        pieceCountColIndex = colNumber;
      }
    });
    
    // 验证必要列
    if (provinceColIndex === -1) {
      throw new Error('未找到包含"省份"或"地区"的列');
    }
    
    if (weightColIndex === -1) {
      throw new Error('未找到包含"重量"或"kg"的列');
    }
    
    // 如果没有件数列，默认1件
    if (pieceCountColIndex === -1) {
      console.warn('⚠️ 未找到件数列，将默认按1件计算');
    }
    
    // 处理数据行（从第2行开始）
    const totalRows = worksheet.rowCount;
    let processedRows = 0;
    let batch = [];
    
    // 使用迭代器处理，减少内存占用
    for await (const row of worksheet.eachRow({ includeEmpty: false })) {
      // 跳过表头
      if (row.number === 1) continue;
      
      try {
        // 获取行数据
        const provinceValue = row.getCell(provinceColIndex).value;
        const weightValue = row.getCell(weightColIndex).value;
        const pieceCountValue = pieceCountColIndex !== -1 ? row.getCell(pieceCountColIndex).value : 1;
        
        // 验证数据
        if (!provinceValue) {
          row.getCell(headerRow.cellCount + 1).value = '';
          row.getCell(headerRow.cellCount + 2).value = '省份信息为空';
          continue;
        }
        
        const weight = parseFloat(weightValue);
        if (isNaN(weight) || weight < 0) {
          row.getCell(headerRow.cellCount + 1).value = '';
          row.getCell(headerRow.cellCount + 2).value = '重量无效（必须是非负数）';
          continue;
        }
        
        const pieceCount = pieceCountValue ? parseInt(pieceCountValue) : 1;
        if (isNaN(pieceCount) || pieceCount < 1) {
          row.getCell(headerRow.cellCount + 1).value = '';
          row.getCell(headerRow.cellCount + 2).value = '件数无效（必须是正整数）';
          continue;
        }
        
        // 查找省份计算器
        const calculator = provinceCalculators.get(String(provinceValue).trim()) || 
                          provinceCalculators.get(String(provinceValue).trim().toLowerCase());
        
        if (!calculator) {
          row.getCell(headerRow.cellCount + 1).value = '';
          row.getCell(headerRow.cellCount + 2).value = `未找到省份"${provinceValue}"的费率配置`;
          continue;
        }
        
        // 计算运费
        const freight = calculator.calculate(weight, pieceCount);
        row.getCell(headerRow.cellCount + 1).value = freight;
        row.getCell(headerRow.cellCount + 1).numFmt = '0.00'; // 保留两位小数
        row.getCell(headerRow.cellCount + 2).value = '计算成功';
        
      } catch (err) {
        row.getCell(headerRow.cellCount + 1).value = '';
        row.getCell(headerRow.cellCount + 2).value = `计算失败: ${err.message}`;
      }
      
      // 批量处理，减少IO操作
      processedRows++;
      batch.push(row);
      
      if (batch.length >= BATCH_SIZE || processedRows >= totalRows - 1) {
        // 刷新批次数据
        await Promise.all(batch.map(r => r.commit()));
        batch = [];
        
        // 记录进度（每处理1000行记录一次）
        if (processedRows % 1000 === 0) {
          console.log(`🔄 任务${taskId}处理进度: ${processedRows}/${totalRows - 1}行`);
        }
      }
    }
    
    // 保存结果文件
    await workbook.xlsx.writeFile(outputPath);
    console.log(`📊 任务${taskId}处理完成，共处理${processedRows}行数据`);
    
    // 清理临时文件
    if (fs.existsSync(inputPath)) {
      fs.unlinkSync(inputPath);
    }
    
    return true;
  } catch (err) {
    console.error(`❌ 处理文件时出错:`, err);
    
    // 创建包含错误信息的结果文件
    const errorWorkbook = new ExcelJS.Workbook();
    const errorWorksheet = errorWorkbook.addWorksheet('错误信息');
    errorWorksheet.getCell('A1').value = '处理失败';
    errorWorksheet.getCell('A2').value = `错误信息: ${err.message}`;
    errorWorksheet.getCell('A3').value = '请检查输入文件格式和内容后重试';
    await errorWorkbook.xlsx.writeFile(outputPath);
    
    throw err;
  }
}

module.exports = router;
